/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.yunhoo.pay.bean;

import com.ibatis.dao.client.DaoManager;
import com.yunhoo.base.bean.FinancialProjectBean;
import com.yunhoo.base.db.dao.DepartmentDao;
import com.yunhoo.base.db.dao.FinancialProjectDao;
import com.yunhoo.base.model.ClassCode;
import com.yunhoo.base.model.CostItem;
import com.yunhoo.base.model.Department;
import com.yunhoo.base.model.FinancialProject;
import com.yunhoo.pay.model.QueryVouchers;
import com.yunhoo.pay.model.Statement;
import com.yunhoo.sys.db.config.DaoConfig;
import com.yunhoo.sys.db.util.IbatisTemplate;
import com.yunhoo.sys.util.common.LoginParam;
import com.yunhoo.sys.util.common.Null;
import com.yunhoo.sys.util.common.ToolKit;
import com.yunhoo.sys.util.tree.TreeUtil;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.SessionScoped;
import javax.faces.context.FacesContext;
import javax.faces.model.DataModel;
import javax.faces.model.ListDataModel;
import javax.servlet.ServletContext;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFooter;
import org.apache.poi.hssf.usermodel.HSSFHeader;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.richfaces.event.TreeSelectionChangeEvent;

/**
 *
 * @author Administrator
 */
@ManagedBean(name = "audit")
@SessionScoped
public class StatementBean {

    private String[] selected;
    private String[] co;
    private List<Statement> statement = new ArrayList();
    private List report = new ArrayList();
    private List<String> projcode = new ArrayList();
    private List<String> eoc = new ArrayList();
    private List<String> deptcode = new ArrayList();
    private List<String> costtype = new ArrayList();
    private List<String> costclass = new ArrayList();
    private List<FinancialProject> projLi;
    private List<CostItem> ecoLi;
    private List<Department> deptLi;
    private List<ClassCode> ctypeLi;
    private QueryVouchers qstat = new QueryVouchers();
    private boolean export = true;
    Map<String, String> mapProj;
    Map<String, String> mapEoc;
    Map<String, String> mapCtype;
    Map<String, String> mapDept;
    Map<String, String> mapClass;
    private int[] colIndex;
    private Object[][] arrt;
    private static String nameSpace = "audit.";
    private static DaoManager daom;
    private static DepartmentDao dao;
    private static FinancialProjectDao finProjdao;
    private DataModel<FinancialProject> finProjModel = null;

    public StatementBean() {
        daom = DaoConfig.getDaoManager();
        dao = (DepartmentDao) daom.getDao(DepartmentDao.class);
        finProjdao = (FinancialProjectDao) daom.getDao(FinancialProjectDao.class);
        initFinProjList();
    }

    private void initFinProjList() {
        if (finProjModel == null) {
            finProjModel = new ListDataModel<FinancialProject>();
        }
        try {
            List<FinancialProject> finli = finProjdao.queryAll(new FinancialProject(true));
            finProjModel.setWrappedData(finli);
        } catch (SQLException ex) {
            Logger.getLogger(FinancialProjectBean.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    //选择预算项目

    public void selectProjForQuery() {
        FinancialProject fProj = finProjModel.getRowData();
        qstat.getProject().setFinCode(fProj.getFinCode());
        qstat.getProject().setFinName(fProj.getFinName());
    }
    //选择部门

    public void selecteDeptForQuery(TreeSelectionChangeEvent event) {
        Department dp = (Department) TreeUtil.listener(event);
        qstat.getDept().setCode(dp.getCode());
        qstat.getDept().setName(dp.getName());
    }

    public void checkandRep() {
        if (selected != null && selected.length > 0) {
            show();
            export = false;
        } else {
            ToolKit.msgs_err("请选择统计项目!");
            FacesContext.getCurrentInstance().renderResponse();
        }

    }

    public void show() {
        report.clear();
        projcode.clear();
        eoc.clear();
        deptcode.clear();
        costtype.clear();
        costclass.clear();

        String para = "";
        for (String s : selected) {
            para += s + ",";
        }

        if (para != null && para.length() > 0) {
            para = para.substring(0, para.length() - 1);
        }


        Map map = new HashMap();
        map.put("par", para.trim());
        map.put("col", para.trim());

        map.put("number", qstat.getNumber().trim());
        map.put("affair", qstat.getAffair().trim());
        map.put("projcode", Null.convert(qstat.getProject().getFinCode()).trim());
        map.put("deptcode", Null.convert(qstat.getDept().getCode()).trim());
        map.put("cpsn_name", Null.convert(qstat.getUser().getCpsn_number()).trim());
        map.put("budgetSource", qstat.getBudgetSource().trim());
        map.put("costcode", Null.convert(qstat.getCostitem().getCode()).trim());
        map.put("paycode", qstat.getPaycode().trim());
        map.put("costClass", qstat.getCostClass().trim());
        map.put("costType", qstat.getCostTypes().getCode().trim());
        map.put("payStyle", qstat.getPayStyle().trim());
        map.put("ticketcode", qstat.getTicketcode().trim());
        map.put("numDateStart", qstat.getNumDateStart());
        map.put("numDateEnd", qstat.getNumDateEnd());
        map.put("auditDateStart", qstat.getAuditDateStart());
        map.put("auditDateEnd", qstat.getAuditDateEnd());
        map.put("payDateStart", qstat.getPayDateStart());
        map.put("payDateEnd", qstat.getPayDateEnd());
        map.put("totalStart", qstat.getTotalStart());
        map.put("totalEnd", qstat.getTotalEnd());
        map.put("checkaccountStart", qstat.getCheckaccountStart());
        map.put("checkaccountEnd", qstat.getCheckaccountEnd());
        map.put("payaccountStart", qstat.getPayaccountStart());
        map.put("payaccountEnd", qstat.getPayaccountEnd());
        map.put("orgCode", curOrgCode().trim());
        statement = IbatisTemplate.queryForList(nameSpace + "queryVouchersForStat", map);

        selectForMap();

        if (projcode != null && projcode.size() > 0) {
            functionProj();
            mapProj = buildProjName();
        }
        if (eoc != null && eoc.size() > 0) {
            functionEoc();
            mapEoc = buildEocName();
        }
        if (deptcode != null && deptcode.size() > 0) {
            functionDept();
            mapDept = buildDeptName();
        }
        if (costtype != null && costtype.size() > 0) {
            functionCtype();
            mapCtype = buildCtypeName();
        }
        if (costclass != null && costclass.size() > 0) {
            mapClass = buildCostClassName();
        }

        for (Statement stmt : statement) {
            Object[] line = new Object[selected.length + 1];

            for (int i = 0; i < selected.length; i++) {
                if (selected[i].equalsIgnoreCase("eoc")) {
                    if (stmt.getEoc() != null) {
                        line[i] = mapEoc.get(stmt.getEoc());
                    } else {
                        line[i] = stmt.getEoc();
                    }
                }
                if (selected[i].equalsIgnoreCase("budgetsource")) {
                    line[i] = stmt.getBudgetsource();
                }
                if (selected[i].equalsIgnoreCase("costtype")) {
                    if (stmt.getCosttype() != null) {
                        line[i] = mapCtype.get(stmt.getCosttype());
                    } else {
                        line[i] = stmt.getCosttype();
                    }
                }
                if (selected[i].equalsIgnoreCase("costclass")) {
                    if (stmt.getCostclass() != null) {
                        line[i] = mapClass.get(stmt.getCostclass());
                    } else {
                        line[i] = stmt.getCostclass();
                    }
                }
                if (selected[i].equalsIgnoreCase("projcode")) {
                    if (stmt.getProjcode() != null) {
                        line[i] = mapProj.get(stmt.getProjcode());
                    } else {
                        line[i] = stmt.getProjcode();
                    }
                }
                if (selected[i].equalsIgnoreCase("deptcode")) {
                    if (stmt.getDeptcode() != null) {
                        line[i] = mapDept.get(stmt.getDeptcode());
                    } else {
                        line[i] = stmt.getDeptcode();
                    }
                }
            }
            line[selected.length] = stmt.getCheckaccount();
            report.add(line);
            buildColIndex(line.length);
            for (int i = 0; i < line.length; i++) {
                //System.out.print(line[i] + " ; ");
            }
        }

        co = new String[selected.length + 1];
        for (int i = 0; i < selected.length; i++) {
            if (selected[i].equalsIgnoreCase("eoc")) {
                co[i] = "经济分类";
            }
            if (selected[i].equalsIgnoreCase("budgetsource")) {
                co[i] = "预算来源";
            }
            if (selected[i].equalsIgnoreCase("costtype")) {
                co[i] = "支出渠道";
            }
            if (selected[i].equalsIgnoreCase("costclass")) {
                co[i] = "支处类别";
            }
            if (selected[i].equalsIgnoreCase("projcode")) {
                co[i] = "项目";
            }
            if (selected[i].equalsIgnoreCase("deptcode")) {
                co[i] = "部门";
            }
        }
        co[selected.length] = "金额";


        FacesContext.getCurrentInstance().renderResponse();
    }

    public void selectForMap() {
        for (Statement stmt : statement) {
            //System.out.println("projcode:" + stmt.getProjcode());
            //System.out.println("eoc:" + stmt.getEoc());
            //System.out.println("deptcode:" + stmt.getDeptcode());
            if (stmt.getProjcode() != null && !stmt.getProjcode().equals("")) {
                projcode.add(stmt.getProjcode().trim());
            }
            if (stmt.getEoc() != null && !stmt.getEoc().equals("")) {
                eoc.add(stmt.getEoc().trim());
            }
            if (stmt.getDeptcode() != null && !stmt.getDeptcode().equals("")) {
                deptcode.add(stmt.getDeptcode().trim());
            }
            if (stmt.getCosttype() != null && !stmt.getCosttype().equals("")) {
                costtype.add(stmt.getCosttype());
            }
            if (stmt.getCostclass() != null && !stmt.getCostclass().equals("")) {
                costclass.add(stmt.getCostclass());
            }
        }
    }

    public void functionProj() {
        String paramproj = "";
        for (String s : projcode) {
            paramproj += s + ",";
        }
        if (paramproj != null && paramproj.length() > 0) {
            paramproj = paramproj.substring(0, paramproj.length() - 1);
        }
        Map map = paramMap();
        map.put("paramproj", paramproj);
        projLi = IbatisTemplate.queryForList(nameSpace + "queryProjname", map);
    }

    public void functionEoc() {
        String parameoc = "";
        for (String s : eoc) {
            parameoc += s + ",";
        }
        if (parameoc != null && parameoc.length() > 0) {
            parameoc = parameoc.substring(0, parameoc.length() - 1);
        }
        Map map = paramMap();
        map.put("parameoc", parameoc);
        ecoLi = IbatisTemplate.queryForList(nameSpace + "queryEocname", map);
    }

    public void functionDept() {
        String paramdept = "";
        for (String s : deptcode) {
            paramdept += s + ",";
        }
        if (paramdept != null && paramdept.length() > 0) {
            paramdept = paramdept.substring(0, paramdept.length() - 1);
        }
        Map map = paramMap();
        map.put("paramdept", paramdept);
        deptLi = IbatisTemplate.queryForList(nameSpace + "queryDeptname", map);
    }

    public void functionCtype() {
        String paramctype = "";
        for (String s : costtype) {
            paramctype += s + ",";
        }
        if (paramctype != null && paramctype.length() > 0) {
            paramctype = paramctype.substring(0, paramctype.length() - 1);
        }
        Map map = new HashMap();
        map.put("paramctype", paramctype);
        map.put("orgCode", curOrgCode().trim());
        ctypeLi = IbatisTemplate.queryForList(nameSpace + "queryCTypename", map);
    }

    public void cancel() {
        qstat = new QueryVouchers();
    }

    public Map<String, String> buildProjName() {
        Map<String, String> map = new HashMap<String, String>();
        for (FinancialProject p : projLi) {
            map.put(p.getFinCode(), p.getFinName());
        }
        return map;
    }

    public Map<String, String> buildEocName() {
        Map<String, String> map = new HashMap<String, String>();
        for (CostItem c : ecoLi) {
            map.put(c.getCode(), c.getName());
        }
        return map;
    }

    public Map<String, String> buildDeptName() {
        Map<String, String> map = new HashMap<String, String>();
        for (Department c : deptLi) {
            map.put(c.getCode(), c.getName());
        }
        return map;
    }

    public Map<String, String> buildCtypeName() {
        Map<String, String> map = new HashMap<String, String>();
        for (ClassCode c : ctypeLi) {
            map.put(c.getCode(), c.getName());
        }
        return map;
    }

    public Map<String, String> buildCostClassName() {
        Map<String, String> map = new HashMap<String, String>();
        map.put("0", "基本支出");
        map.put("1", "项目支出");
        return map;
    }

    private static Map paramMap() {
        Map map = new HashMap();
        map.put("orgCode", LoginParam.getOrg().getCode().trim());
        map.put("iyear", LoginParam.getIyear());
        return map;
    }

    public void exportExcel() {
        //System.out.println("导出Excel");
        HSSFWorkbook demoWorkBook = new HSSFWorkbook();
        HSSFSheet demoSheet = demoWorkBook.createSheet("统计报表");
        String[] excelColumns = co;
        List rows = report;

        HSSFHeader header = demoSheet.getHeader();
        header.setCenter("统计表表头");
        HSSFRow headerRow = demoSheet.createRow(1);
        for (int i = 0; i < excelColumns.length; i++) {
            HSSFCell headerCell = headerRow.createCell((int) i);
            //headerCell.setEncoding(HSSFCell.ENCODING_UTF_16);
            headerCell.setCellValue(excelColumns[i]);
        }
        //填充数据
        int rowIndex = 2;
        for (int i = 0; i < rows.size(); i++) {
            Object[] row = (Object[]) rows.get(i);
            createTableRow(demoWorkBook, demoSheet, row, rowIndex);
            rowIndex++;
        }
//        for(Object[] row:rows){
//            createTableRow(demoSheet,row,rowIndex);
//            rowIndex++;
//        }
        StatementBean.exportExcel(demoWorkBook, demoSheet);
    }
    //初始化表头信息

    public String[] initTabeleHeader() {
        for (int i = 0; i < selected.length; i++) {
            String s = "" + selected[i] + "";
        }
        String[] excelColumns = {"功能科目代码", "功能科目名称", "项目代码", "项目名称",
            "收入合计", "收入（零余额）", "上年结转小计", "上年结转（零余额）", "本年预算", "其他收入",
            "执行合计", "财政拨款支出", "其中：零余额支出", "其他支出", "余额", "财政拨款余额", "余额（零余额）", "其他结余"};
        return excelColumns;
    }

    public void createTableRow(HSSFWorkbook demoWorkBook, HSSFSheet demoSheet, Object[] sta, int rowIndex) {
        //创建第rowIndex行
        HSSFCellStyle dstyle = demoWorkBook.createCellStyle();
        dstyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));
        HSSFCell cell = null;
//        //System.out.println("sta.size:"+sta.length);
//        //System.out.println("co.size:"+co.length);
        HSSFRow row = demoSheet.createRow(rowIndex);
        for (int i = 0; i < co.length - 1; i++) {
            cell = row.createCell(i);
            cell.setCellValue((String) sta[i]);
        }
        cell = row.createCell(co.length - 1);
        cell.setCellValue((Double) sta[co.length - 1]);
        cell.setCellStyle(dstyle);
    }

    public static void exportExcel(HSSFWorkbook demoWorkBook, HSSFSheet demoSheet) {
        try {
            String strfileName = "book.xls";
            FileOutputStream fos = null;
            //导出
            demoSheet.setGridsPrinted(true);//demoSheet
            HSSFFooter footer = demoSheet.getFooter();
            footer.setRight("Page " + HSSFFooter.page() + " of " + HSSFFooter.numPages());
            // 获得JSF上下文环境
            FacesContext context = FacesContext.getCurrentInstance();
            // 获得ServletContext对象
            ServletContext servletContext = (ServletContext) context.getExternalContext().getContext();
            // 取得文件的绝对路径
            String excelName = servletContext.getRealPath("/upload") + "/" + strfileName;//strfileName
            File exportFile = new File(excelName);
            fos = new FileOutputStream(exportFile);//fos
            demoWorkBook.write(fos);//demoWorkBook
            fos.close();

            FacesContext ctx = FacesContext.getCurrentInstance();
            ctx.responseComplete();
            HttpServletResponse httpServletResponse = (HttpServletResponse) ctx.getExternalContext().getResponse();
            ServletOutputStream servletOutputStream = httpServletResponse.getOutputStream();
            httpServletResponse.setHeader("Content-disposition", "attachment; filename=" + strfileName);//strfileName
            httpServletResponse.setContentLength((int) exportFile.length());//exportFile
            httpServletResponse.setContentType("application/x-download");
            //httpServletResponse.setContentType("application/vnd.ms-excel");
            byte[] b = new byte[1024];
            int i = 0;
            FileInputStream fis = new java.io.FileInputStream(exportFile);
            while ((i = fis.read(b)) > 0) {
                servletOutputStream.write(b, 0, i);
            }
            servletOutputStream.flush();
            ctx.responseComplete();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void buildColIndex(int colSize) {
        colIndex = new int[colSize];
        for (int i = 0; i < colSize; i++) {
            colIndex[i] = i;
        }
        //System.out.println("buildColIndex : " + colIndex.length);
    }

    public String[] getSelected() {
        return selected;
    }

    public void setSelected(String[] selected) {
        this.selected = selected;
    }

    public List<Statement> getStatement() {
        return statement;
    }

    public void setStatement(List<Statement> statement) {
        this.statement = statement;
    }

    public List getReport() {
        return report;
    }

    public void setReport(List report) {
        this.report = report;
    }

    public String[] getCo() {
        return co;
    }

    public void setCo(String[] co) {
        this.co = co;
    }

    public List<String> getCosttype() {
        return costtype;
    }

    public void setCosttype(List<String> costtype) {
        this.costtype = costtype;
    }

    public List<String> getDeptcode() {
        return deptcode;
    }

    public void setDeptcode(List<String> deptcode) {
        this.deptcode = deptcode;
    }

    public List<String> getEoc() {
        return eoc;
    }

    public void setEoc(List<String> eoc) {
        this.eoc = eoc;
    }

    public List<String> getProjcode() {
        return projcode;
    }

    public void setProjcode(List<String> projcode) {
        this.projcode = projcode;
    }

    public QueryVouchers getQstat() {
        return qstat;
    }

    public void setQstat(QueryVouchers qstat) {
        this.qstat = qstat;
    }

    public boolean isExport() {
        return export;
    }

    public void setExport(boolean export) {
        this.export = export;
    }

    public int[] getColIndex() {
        return colIndex;
    }

    public void setColIndex(int[] colIndex) {
        this.colIndex = colIndex;
    }

    public Object[][] getArrt() {
        return arrt;
    }

    public void setArrt(Object[][] arrt) {
        this.arrt = arrt;
    }

    public List<String> getCostclass() {
        return costclass;
    }

    public void setCostclass(List<String> costclass) {
        this.costclass = costclass;
    }

    public List<ClassCode> getCtypeLi() {
        return ctypeLi;
    }

    public void setCtypeLi(List<ClassCode> ctypeLi) {
        this.ctypeLi = ctypeLi;
    }

    public List<Department> getDeptLi() {
        return deptLi;
    }

    public void setDeptLi(List<Department> deptLi) {
        this.deptLi = deptLi;
    }

    public List<CostItem> getEcoLi() {
        return ecoLi;
    }

    public void setEcoLi(List<CostItem> ecoLi) {
        this.ecoLi = ecoLi;
    }

    public Map<String, String> getMapClass() {
        return mapClass;
    }

    public void setMapClass(Map<String, String> mapClass) {
        this.mapClass = mapClass;
    }

    public Map<String, String> getMapCtype() {
        return mapCtype;
    }

    public void setMapCtype(Map<String, String> mapCtype) {
        this.mapCtype = mapCtype;
    }

    public Map<String, String> getMapDept() {
        return mapDept;
    }

    public void setMapDept(Map<String, String> mapDept) {
        this.mapDept = mapDept;
    }

    public Map<String, String> getMapEoc() {
        return mapEoc;
    }

    public void setMapEoc(Map<String, String> mapEoc) {
        this.mapEoc = mapEoc;
    }

    public Map<String, String> getMapProj() {
        return mapProj;
    }

    public void setMapProj(Map<String, String> mapProj) {
        this.mapProj = mapProj;
    }

    public static String getNameSpace() {
        return nameSpace;
    }

    public static void setNameSpace(String nameSpace) {
        StatementBean.nameSpace = nameSpace;
    }

    public List<FinancialProject> getProjLi() {
        return projLi;
    }

    public void setProjLi(List<FinancialProject> projLi) {
        this.projLi = projLi;
    }

    public DataModel<FinancialProject> getFinProjModel() {
        return finProjModel;
    }

    public void setFinProjModel(DataModel<FinancialProject> finProjModel) {
        this.finProjModel = finProjModel;
    }

    private String curOrgCode() {
        return LoginParam.getOrg().getCode();
    }
}
